# File Name: createData.py
# Author: Daniel Squires
# Created: 10/11/12
# This script writes sql scripting to load data into the database

import random
import string

# Dictionary of the vendor

vendors = {'00010':'Moobys','00001':'Kwik-E-Mart','00002':'Torchwood','00003':'Mos Eisley Cantina','00004':'Graystone Industries','00005':'Part R Us','00006':'R Soles','00007':'Corleone Olive Oil','00008':'Moes Tavern','00009':'Bluth Company'}

users = ['Mark Blunderbutt', 'Tvs Frank', 'Clayton Forrestor', 'Hefty Smurf', 'CrowT Robot', 'Tom Servo', 'Ozzy Osborne', 'Big Bird', 'Howard Duck', 'Joel Robinson']

# List of the parts
parts = []

# List if finished goods
fg = []



passChars = string.ascii_uppercase + string.digits + string.ascii_lowercase
partsChars = string.ascii_uppercase + string.digits
fileName = "marssneers.sql"

def createTables(mode):
    drop = """DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Parts;
DROP TABLE IF EXISTS Vendors;
DROP TABLE IF EXISTS Parts_Required;
DROP TABLE IF EXISTS Finished_Goods;
DROP TABLE IF EXISTS Purchase_Items;
DROP TABLE IF EXISTS Purchase_Orders;
DROP TABLE IF EXISTS Order_Requests;
DROP TABLE IF EXISTS New_Accounts;
DROP TABLE IF EXISTS Password_Requests;

CREATE TABLE Users(
	user_id int(5) ZEROFILL AUTO_INCREMENT,
	first_name varchar(20) NOT NULL,
	last_name varchar(20) NOT NULL,
	login varchar(100) NOT NULL,
	password varchar(100) NOT NULL,
	manager boolean NOT NULL DEFAULT 0,
	admin boolean NOT NULL DEFAULT 0,
	PRIMARY KEY (user_id)
);

CREATE TABLE Vendors (
	vendor_id int(5) ZEROFILL AUTO_INCREMENT,
	company_name varchar(50) NOT NULL,
	PRIMARY KEY (vendor_id)
);

CREATE TABLE Parts (
	part_id varchar(20) NOT NULL,
	vendor_id int(5) NOT NULL,
	cost numeric(5,2) NOT NULL,
	part_description varchar(255),
	inventory int(10) NOT NULL,
	PRIMARY KEY (part_id, vendor_id)
);

CREATE TABLE Parts_Required (
	fg_id varchar(20) NOT NULL,
	part_id varchar(20) NOT NULL,
	quantity int(5) NOT NULL,
	PRIMARY KEY (fg_id, part_id)	
);

CREATE TABLE Finished_Goods (
	fg_id varchar(20) NOT NULL,
	description varchar(255) NOT NULL,
	PRIMARY KEY (fg_id)
);

CREATE TABLE Purchase_Items (
	order_id int(5) NOT NULL,
	part_id varchar(20) NOT NULL,
	quantity int(10) NOT NULL,
	PRIMARY KEY (order_id, part_id)
);

CREATE TABLE Purchase_Orders (
	order_id int(5) NOT NULL AUTO_INCREMENT,
	user_id int(5) ZEROFILL,
	quantity int(10) NOT NULL,
	submit_date date NOT NULL,
	manager_id int(5) ZEROFILL,
	fulfillment_date date,
	total_cost numeric(5,2) NOT NULL,
        approved boolean DEFAULT 0,
	PRIMARY KEY (order_id)
);

CREATE TABLE Order_Requests (
	request_id varchar(20) NOT NULL,
	part_id varchar(20) NOT NULL,
	user_id int(5) ZEROFILL,
	quantity int(10) NOT NULL,
	date date NOT NULL,
	PRIMARY KEY (request_id)
);

CREATE TABLE New_Accounts (
	email varchar(100) NOT NULL,
	first_name varchar(20),
	last_name varchar(20),
	PRIMARY KEY (email)
);

CREATE TABLE Password_Requests (
	email varchar(100) NOT NULL,
	PRIMARY KEY (email)
);

"""
    f = open(fileName, mode)
    f.write(drop)
    f.close()

##########################################################################

def createUsers(mode):
    f = open(fileName, mode)
    f.write("/* user_id, first_name, last_name, login, password, manager, administrator */\n")
    for user in users:
        fName, lName = user.split()
        login = fName.lower() + "-" + lName.lower() + "@marssneers.com"
        password = ''.join(random.choice(passChars) for x in range(8))
        f.write("INSERT INTO Users VALUES('', '" + fName + "', '" + lName + "', '" + login + "', '" + password + "', '0', '0');\n")
    f.write("\n")
    f.close()

def createParts(mode):
    f = open(fileName, mode)
    
    f.write("/* part_id, vendor_id, cost, part_description, inventory */\n")
    for vendorID, name in vendors.items():
        numOfParts = random.randint(10,99) # for each vendor create a random amount of parts
        for x in range(numOfParts):
            vendorParts = []
            # Create random unique part numbers
            partNumber = ''.join(random.choice(partsChars) for x in range(10))
            #while partNumber in partNumbers:
            while partNumber in vendorParts:
                partNumber = ''.join(random.choice(partsChars) for x in range(10))
            
            cost = str(round(random.uniform(0, 200), 2)) # Cost of part
            inventory = str(random.randint(0, 100)) # Amount of inventory on hand
            partDesc = "Vendor: " + vendorID + ". Part number:" + partNumber
            
            f.write("INSERT INTO Parts VALUES('" + partNumber + "', '" + vendorID + "', '" + cost + "', '" +  partDesc + "', '" + inventory + "');\n")
            parts.append(partNumber)
    f.write("\n")
    f.close()

def createPartsReq(mode):
    f = open(fileName, mode)
    f.write("/* fg_id, part_id, quantity */\n")    
    for fg_id in fg:
        numOfParts = random.randint(1, 25)
        myParts = []
        for i in range(numOfParts):
            qty = random.randint(1, 10)
            partReq = random.choice(parts)
            while partReq in myParts:
                partReq = random.choice(parts)
            myParts.append(partReq)
            f.write("INSERT INTO Parts_Required VALUES('" + fg_id + "', '" + partReq + "', '" + str(qty) + "');\n")
    #f.write("\n")
    f.close()
        

def createVendors(mode):
    f = open(fileName, mode)
    f.write("/* vender_id, company_name */\n")
    for vendorID, name in vendors.items():
        f.write("INSERT INTO Vendors VALUES('', '" + name + "');\n")
    f.write("\n")
    f.close()

def createFinishedGoods(mode):
    f = open(fileName, mode)
    f.write("/* fg_id, company_name */\n")
    for i in range(50):
        fg_id = ""
        if i < 10:
            fg_id = '0000'
        else:
            fg_id = '000'
        fg_id = fg_id + str(i)
        fg.append(fg_id)
        f.write("INSERT INTO Finished_Goods VALUES('" + fg_id + "', 'Widget " + str(i) + "');\n")
    f.write("\n")
    f.close()
    
def createNewAccountRequests(mode):
    f = open(fileName, mode)
    f.write("/* email, first_name, last_name */\n")
    f.write("INSERT INTO New_Accounts VALUES('darth-vador@marssneers.com', 'Anakin', 'Skywalker');\n")
    f.write("INSERT INTO New_Accounts VALUES('homey-theclown@marssneers.com', 'Homey', 'Clown');\n")
    f.write("\n")
    f.close()
    
def createPassRequests(mode):
    f = open(fileName, mode)
    f.write("/* email */\n")
    f.write("INSERT INTO Password_Requests VALUES('hefty-smurf@marssneers.com');\n")
    f.write("INSERT INTO Password_Requests VALUES('tom-servo@marssneers.com');\n")
    f.write("\n")
    f.close()

def createPurchaseOrders(mode):
    f = open(fileName, mode)
    f.write("/* order_id, user_id, quantity, submit_date, manager_id, fulfillment_date, total_cost, approved */\n")    
    f.write("INSERT INTO Purchase_Orders VALUES('', '00002', '23', '2011-12-25', '00006', '', '123.45', '0');\n")
    f.write("\n")
    f.close()    
    
createTables('w')
createUsers('a')
createVendors('a')
createFinishedGoods('a')
createNewAccountRequests('a')
createPassRequests('a')
createParts('a')
createPartsReq('a')
createPurchaseOrders('a')